﻿Imports System.Data.OleDb
Imports System.Web.UI.WebControls
Imports System.Web



Partial Public Class Details
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim cnt As Integer = 0

        Dim conn = New OleDbConnection(ConfigurationManager.ConnectionStrings _
                ("MovieConnectionString").ToString())
        Dim mid As String = Request.QueryString("movieID").ToString()

        Dim sqlCount As String = " SELECT  [visitorcount] FROM [moviedetails]where movieID= " & mid


        Dim Cmd As New OleDbCommand(sqlCount, conn)
        conn.Open()
        Dim dbread As OleDbDataReader = Cmd.ExecuteReader
        dbread.Read()
        cnt = dbread("visitorcount")
        cnt = cnt + 1
        Dim sql As String = "UPDATE  [moviedetails] SET [visitorcount] = " + cnt.ToString() + " where movieID =" & mid

        Dim dbcmd As New OleDbCommand(sqlCount, conn)
        dbcmd = New OleDbCommand(sql, conn)
        dbcmd.ExecuteNonQuery()
        conn.Close()
        Label1.Text = cnt

        Dim con As OleDb.OleDbConnection = New OleDb.OleDbConnection(ConfigurationManager.ConnectionStrings("MovieConnectionString").ToString())
        con.Open()

        Dim sqlString As String = "select m.moviename, md.description, [md.cast], md.musicdirector, md.director, md.productionhouse, md.images, md.budjet, md.yearofrelease,m.ratings from [moviedetails] as md inner join [movies] as m on md.movieID=m.movieID where md.movieId=" + mid + ""
        Dim cmd1 As OleDb.OleDbCommand = New OleDb.OleDbCommand(sqlString, con)
        Dim reader As OleDb.OleDbDataReader = cmd1.ExecuteReader()
        While reader.Read()
            lbmoviename.Text = reader.Item(0).ToString()
            lbdescription.Text = reader.Item(1).ToString()
            lbcast.Text = reader.Item(2).ToString()
            lbmusicdirector.Text = reader.Item(3).ToString()
            lbDirector.Text = reader.Item(4).ToString()
            lbproductionhouse.Text = reader.Item(5).ToString()
            lbimgmovie.ImageUrl = reader.Item(6).ToString()
            lbbudjet.Text = reader.Item(7).ToString()
            lbyearofrelease.Text = reader.Item(8).ToString()

        End While

        reader.Close()
        cmd1.Dispose()
        avgrating.CurrentRating = getAverageUserRating().ToString
    End Sub


    Protected Sub btnComment_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnComment.Click
        Dim id = Request.QueryString("MovieID")
        Dim Conn As New OleDbConnection(ConfigurationManager.ConnectionStrings("MovieConnectionString").ToString())
        Conn.Open()
        Dim sql As String = "INSERT INTO Comments (Author,comment,MovieID) VALUES(@Author,@comment,@MovieID)"
        Dim cmd As New OleDbCommand(sql, Conn)
        cmd.Parameters.AddWithValue("@Author", User.Identity.Name)
        cmd.Parameters.AddWithValue("@comment", txtb_comment.Text)
        cmd.Parameters.AddWithValue("@MovieID", id)
        cmd.ExecuteNonQuery()
        Conn.Close()

        Repeater1.DataBind()
        txtb_comment.Text = ""

    End Sub

    Protected Sub bt_Tags_Click(ByVal sender As Object, ByVal e As EventArgs) Handles bt_Tags.Click
        Dim movieid = Request.QueryString("Movieid")
        Dim conn As New OleDbConnection(ConfigurationManager.ConnectionStrings("MovieConnectionString").ToString())

        conn.Open()
        For Each item As ListItem In CheckBoxList1.Items
            If item.Selected Then
                Dim sql As String = "INSERT INTO TagsbyJunction ( Movieid,Tagbyjunid) VALUES ( @Movieid, @Tagbyjunid )"
                Dim cmd As New OleDbCommand(sql, conn)
                cmd.Parameters.AddWithValue("@Movieid", movieid)
                cmd.Parameters.AddWithValue("@Tagbyjunid", item.Value)
                cmd.ExecuteNonQuery()
            End If

        Next

        Dim Array() As String = tb_tag.Text.Split(" ")
        For Each tagstring As String In Array
            If tagstring <> "" Then
                Dim NewTagsSql As String = "INSERT INTO Tags (Tag) VALUES (@Tag )"
                Dim NewTagCmd As New OleDbCommand(NewTagsSql, conn)
                NewTagCmd.Parameters.AddWithValue("@Tag", tagstring)
                NewTagCmd.ExecuteNonQuery()
                lblTags.Visible = True
                tb_tag.Text = ""

                Dim NewId As String = "SELECT TOP 1 ID From Tags ORDER BY ID Desc"
                Dim NewIdCmd As New OleDbCommand(NewId, conn)
                Dim dbread As OleDbDataReader = NewIdCmd.ExecuteReader
                dbread.Read()
                Dim IDOfNewTag = dbread("ID")

                Dim tagSql As String = "INSERT INTO TagsbyJunction ( Movieid, Tagbyjunid ) VALUES ( @Movieid, @Tagbyjunid )"
                Dim tagCmd As New OleDbCommand(tagSql, conn)
                tagCmd.Parameters.AddWithValue("@Movieid", movieid)
                tagCmd.Parameters.AddWithValue("@Tagbyjunid", IDOfNewTag)
                tagCmd.ExecuteNonQuery()

            End If
        Next
    End Sub

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
        Dim movieid = Request.QueryString("movieid")

        Dim Conn As New OleDbConnection(ConfigurationManager.ConnectionStrings("MovieConnectionString").ToString())

        Dim del As String = "DELETE FROM rating  WHERE UserName = '" & User.Identity.Name & "' and Movieid = '" & movieid & " ' "
        Dim del_cmd As New OleDbCommand(del, Conn)
        Conn.Open()
        del_cmd.ExecuteNonQuery()

        Dim str As String = "INSERT INTO rating (UserName,Rate,Movieid) VALUES(@UserName,@Rate,@Movieid)"
        Dim cmd As New OleDbCommand(str, Conn)
        cmd.Parameters.AddWithValue("@UserName", User.Identity.Name)
        cmd.Parameters.AddWithValue("@Rate", Rating1.CurrentRating())
        cmd.Parameters.AddWithValue("@Movieid", movieid)
        cmd.ExecuteNonQuery()
        Repeater1.DataBind()
    End Sub

    Protected Function getAverageUserRating()
        Dim conn = New OleDbConnection(ConfigurationManager.ConnectionStrings _
        ("MovieConnectionString").ToString())
        Dim movieid = Request.QueryString("movieid")
        Dim averageSql As String = "SELECT AVG(Rate)as AvgRate FROM rating WHERE Movieid =  '" + movieid + "' "
        Dim averageCmd As New OleDbCommand(averageSql, conn)
        conn.Open()
        Dim averageDbRead = averageCmd.ExecuteReader()
        averageDbRead.Read()
        Try
            Return CInt(averageDbRead.Item("AvgRate"))
        Catch ex As Exception
            Return 0
        End Try
        Repeater1.DataBind()
    End Function
End Class